Fritz: All Fritz

home *** CD-ROM | disk | FTP | other *** search

/ Fritz: All Fritz / All Fritz.zip / All Fritz / FILES / ACCOUTIL / LTSTIPS3.LZH / LOTUSTIP < prev next >

Wrap

Text File | 1985-11-28 | 55KB | 1,007 lines

System Disk Trick (PC Magazine Vol 3 No 17 Sept 4, 1984 User-to-User) For users of Release 1A of 123 on hard disks who do not like to get out their system disk each time they boot 123, from the DOS prompt (or in an AUTOEXEC.BAT file) simply type 123 and hit Enter. This does not work if you load the Lotus Access System before loading 123. When the 123 copyright notice appears, place a blank unformatted disk in drive A: and press any key. The blank 123 spreadsheet appears and away you go. ----------------------------------------------------------------- Transferring Worksheet Ranges (PC World April 1984 The Help Screen) Assume you have two worksheet files called ONE and TWO that are too large to combine in a single file. Calculations in worksheet ONE produce results in the range V102..V152. The method to automatically enter the results in worksheet TWO (assume cells C22..C72) and recalculate worksheet TWO is this. First, load worksheet ONE and move the cell pointer to cell V102). Next, use the command /Range Name Create, enter a name such as TRANSFER LIST, and specify the range V102..V152. Then save worksheet ONE. Now load worksheet TWO, move the cell pointer to C22, and give the command /File Combine Copy NamedRange TRANSFER LIST. The entries in cells V102..V152 of worksheet ONE will be copied into cells C22..C72 of worksheet TWO. If formula recalculatino for worksheet TWO has been set to manual (/Worksheet Global Recalculation Manual), press the F9 key to recalculate worksheet TWO's formulas. This procedure can be abbreviated to two keystrokes by using 123's macros. Load worksheet ONE, move the cell pointer to an empty area, and enter the following macro: {goto}V102~ '/rncTRANSFER LIST~ V102..V152~ '/fsONE~ r '/frTWO~ To name the macro, move the cell pointer to the cell that contains the first macro command. Use the letter T for transfer. Select /Range Name Create, type \T and press Enter twice. Save this worksheet and its newly created macro. You can invoke this macro at any time by pressing <Alt>-T. The remainder of the routine must be in worksheet TWO. For the sake of convenience, refer to it by the same <Alt> key combination as the macro in worksheet ONE. Load worksheet TWO, move the cell pointer to an empty area and enter the macro: {goto}C22~ 'fccnTRANSFER LIST~ ONE~ {calc} Name this macro (/Range Name Create), type \T, press Enter twice, and save worksheet TWO. Because the macros in both worksheets share the same name, whenever worksheet ONE is loaded and 123 is in the Ready mode, you can transfer the list of values from cells V102..V152 in worksheet ONE to cells C22..C72 of worksheet TWO simply by pressing <Alt>-T twice. CORRECTION (from PC World July 1985 The Help Screen): If the range to be copied contains worksheet formulas, these formulas -- rather than their current values -- are transferred to the second worksheet. This does not solve the problem of transferring values. The answer, of course, is to use the /File Combine Add command which adds the value of the cells from the first worksheet to whatever is in the target cells in the second worksheet. It's also a good idea to preface this command with a /Range Erase just to make sure that the cells in the second worksheet are empty. To amend the procedure described above, edit the macro for worksheet TWO to read: {goto}C22~ '/reC22..C72~ '/fcanTRANSFER LIST~ ONE~ {calc} ----------------------------------------------------------------- Leaving a Hole for a Variable (PC Magazine Vol 4 No 13 June 25, 1985 Spreadsheet Clinic) It's useful to be able to put variables into a macro. The trick is to put a "hole" in the macro which is filled before the macro executes. Figure 1 involves two simple macros. You could use it as part of a procedure to input monthly data according to a standard format and save the data with the name of the month as the filename. The first macro prompts for the name of the month and stores the name in cell C1. After you have entered all the data for that month, you invoke the second macro, which copies the name of the month into the "hole" in cell A3. The macro then does a file save, using the name of the month as the filename. The key to using variables in macros is to understand that single commands can be spread over several cells. The file save command that starts in cell A2 doesn't end until cell A4. This is what lets you put a variable in cell A3. Figure 2 is a more complex example, which you might use to adjust all the numbers in a column of figures by multiplying them by a constant. The macro prompts for the constant and the address of the column of figures and away it goes. The range name routine (cells AA6 through AA8) that stops the macro at the last number in the column is from LeBlond and Cobb's book, Using 1-2-3. Editor's Note: An example in User-to-User Vol 3 No 18 used this technique but didn't elaborate on the principle. The macro in Figure 2 could be made even more flexible if there were a third prompt that supplied the operation to be performed on the column of figures (in this case the * in cell AA9). Any of the arithmetic operators would work. There's nothing to stop you from slinging as many variables as you like into your macros. - - - - - Figure 1: Simple Macros -- this procedure creates a slot that can be filled with variable information First Macro /xlWhat is the month of this budget? : ~C1~ Second Macro Cell A1 /cc1~A3~ Cell A2 /fs Cell A3 Cell A4 - - - - - Figure 2: Complex Macro -- This macro carries out an arithmetic function with the constant and cell addresses supplied by the user Cell AA1 /xlWhat is the adjustment? : ~AA10~ Cell AA2 /xlWhat is the column? : ~AA4~ Cell AA3 {goto} Cell AA4 Cell AA5 ~ Cell AA6 /RNCTEMP~~ Cell AA7 /XITEMP=0~/RNDTEMP~/XGAA15~ Cell AA8 /RNDTEMP~~ Cell AA9 {edit}* Cell AA10 Cell AA11 ~ Cell AA12 {edit}{calc}~ Cell AA13 {down} Cell AA14 /xgAA6~ ----------------------------------------------------------------- Don't Justify Named Ranges (PC Magazine Vol 4 No 14 July 9, 1985 Spreadsheet Clinic) Don't use range names when you justify labels. The command "/Range Justify named-range <Enter>" will wipe out your range name. Editor's Note: It won't really wipe out your range name; 123 remembers it. If you try a /Range Name Delete command, 123 will list the range name on the command line. However, the range is no longer valid, and when you try to do something with it (copy, move, etc.), 123 will tell you that it's an illegal range. This is a case of a perfectly legal range being made an outlaw by having its contents rearranged. The same thing will happen if you use the normal cell address technique to justivy labels that happen to contain a named range. 123 will still remember the range but will tell you it's illegal any time you try to do something with it. Justification and named ranges don't mix. ----------------------------------------------------------------- Linked 123 Worksheets (PC World July 1985 Star-Dot-Star) It's not uncommon to have a series of 123 worksheets that must be subjected to an identical group of procedures. This typically involves loading a template, "combining-in" a file, performing various operations, and printing the result. In the past we used a brute-force approach. Today all our files contain a small macro named \0 at cell B1 (any cell will do). The macro consists of {GoTo}B2~/FCCEzero~, and cell B2 is blank. An empty worksheet named Zero is kept in each subdirectory. The effect of the macro is to combine the worksheet Zero below the autoexecuting macro every time the worksheet is loaded. Usually Zero is empty and the macro stops. To perform a series of operations on a group of worksheets, just put the appropriate macro into cell A1 of Zero to initiate the sequence for each worksheet. This allows a worksheet to pass macro keystrokes to another file, a feature not otherwise available in 123. In addition, the File Extract command can put keystrokes into Zero that cause it to retrieve the next worksheet in sequence. ----------------------------------------------------------------- Combining Worksheets (PC Magazine Vol 4 No 17 Aug 20, 1985 Spreadsheet Clinic) It's often necessary to take selected data from a worksheet on disk (call it DISK.WKS) and read it into the worksheet you are currently working on (SCREEN.WKS). If, when you last saved DISK.WKS, you knew exactly what data you would later need in SCREEN.WKS, you could have given that data a range name. Then you could use the /File Combine Copy Named-range command to read the data you need from DISK.WKS into SCREEN.WKS. However, you may need data from DISK.WKS but haven't given that data a range name. In order to get that data, you have to 1) save SCREEN.WKS, 2) retrieve DISK.WKS and find the data, 3) give the data a range name, 4) save DISK.WKS, 5) retrieve SCREEN.WKS, and 6) read the necessary data from DISK.WKS into SCREEN.WKS with /File Combine Copy Named-range. A quicker way, though the first two steps are the same, is: 1) save SCREEN.WKS, 2) retrieve DISK.WKS and find the data, then 3) use /File Xtract Values to put this data in a temporary file, say, TEMP.WKS, and 5) read TEMP.WKS into SCREEN.WKS with /File Combine Copy Entire-file. This saves time for two reasons. /File Combine will retrieve a named range from a large file. Also, since you are not giving DISK.WKS a new range name, you don't have to save it back to disk. Editor's Note: This method is quicker. However, you give up one thing in return for speed: you haven't got a named range in DISK.WKS if you ever need the same data again. You should give your data range names to begin with so you never have to dump/reload the current file. ----------------------------------------------------------------- Shrinking a Spreadsheet for Maximum Memory (PC Magazine Vol 4 No 16 Aug 6, 1985 Spreadsheet Clinic) If you use large 123 models, your computer may run out of memory. One way to get memory back is to get rid of unnecessary cells. If you decide you don't need cells beyond a certain point in the worksheet, go to the cell just below the last useful cell and hit /Range Erase (End) (Home) Enter. This will erase everything between the last useful cell and the last cell in the worksheet. However, you won't get all your memory back just because you erased the cells. If you hit (End) (Home) again, you will find that the cursor ends up at the cell that was last in the spreadsheet before you used /Range Erase. In other words, 123 thinks the spreadsheet is still the same size as before, even though the cells are blank. It will release the memory it was using to store the cell contents but will still require some memory just to keep track of the blank cells it thinks are still part of the worksheet. To get all your memory back do a /File Save and then a /File Retrieve. By reading the worksheet back into memory, 123 figures out that the blank cells really aren't part of the spreadsheet and thus releases the memory it was using to keep track of them. Even this technique won't work at times. Even after the /File Save and /File Retrieve sequence, the (End) (Home) command still may take you to a blank cell (the last one before your /Range Erase) at the bottom of the worksheet. The reason is that some of the now-blank cells were either unprotected or formatted with the /Range Format command. In either case, 123 treats them as if they held data, refusing to give them up, and so wastes memory on them. You can solve this problem by using /Range Protect (End) (Home) and /Range Format Reset (End) (Home). These operations disable the special status of these cells and allow 123 to ignore them. Now, after you have saved and retrieved the worksheet, you will find that you have gotten all your memory back. ----------------------------------------------------------------- Making a Table of 1-2-3 Range Addresses (PC Magazine Vol 4 No 16 Aug 6, 1985 Spreadsheet Clinic) Symphony has a Range Name Table command that lists the cells represented by range names. 123 does not have this feature, but a short macro (Figure 1) will produce the same table that Symphony does and will sort the list of range names alphabetically. To use the macro, first make a list of range names in a column in your worksheet, as in the "before" part of Figure 2. Make sure that there is an empty cell above and below the list and that there are no empty cells within the list. When you are writing the macro, remember to give the range name SORT to the cell where the sort routine begins. To fill in the table with the cells in each range, put the cursor on the first range name of the list and run the macro. This macro is a big help in keeping track of ranges in complex worksheets. Whenever you create a new range, add it to the list of names and rerun the macro. This keeps the documentation current and accurate. - - - - - Figure 1: Macro to create a table of range names, and sort them. \T /c~{right}~ 'Copy this label name to right /rncCC~{bs}.{right}~ 'Name these 2 cells as Current Cells (CC) /xi@count(cc)=0~/xgSORT~ 'If blank, go to SORT step {right}{edit} 'Edit duplicate label name {home}{del}@sum({end})~ 'Convert to @sum(x) formula {edit}{bs}{home} 'Edit again {del}{del}{del}{del}{del}'~ 'Convert formula back to label {left}{down} 'Go to next label name in list /xg\T~ 'Loop back to process next address label SORT: {up}{end}{up} 'Position cursor for sort /dsr 'Reset sort parameters d{bs}.{end}{down}{right}~ 'Define data range p~a~g 'Define sort key ...go - - - - - Figure 2: Printout of a list of range names in a spreadsheet before running the macro above and after, including a display of the cell locations. ===== BEFORE ===== ===== AFTER ===== RANGE CELLS RANGE CELLS NAME NAME LIST LIST ===== ===== ===== ===== SUM A A2..A16 A B C2..G2 C C D5..F13 B SUM C16 ----------------------------------------------------------------- Building a Range Name Table (PC Magazine Vol 4 No 19 Sept 17, 1985 Spreadsheet Clinic) If you work with large spreadsheets with many range names, it can be hard to keep track of them. By hitting F5 and then F3, the range names will appear in the control panel, but it can be cumbersome to scroll through them all, looking for the one you want. A macro will will create range names and build a table of names with a description of the contents of each range. When you set up the heading for the table (lines 3 and 4), it's a good idea to make column D 16 characters wide. That lets you enter range names as long as 123's maximum of 15 characters, with one extra character for separation. The macro requires 3 specified range names. Cell C3 must be called Range Names, cell E18 must be Range-Name:, and E21 must be Scratch. You run the macro whenever you are about to name a range. Put the cursor on the top left cell of the range and invoke the macro with Alt-T. The macro will prompt you you for the range name and a brief description of its contents. It then creates the range name you entered, and ends by letting you define the range by "pointing" with the cursor and hitting Enter. Lines 5 and 6 are samples of the kind of information you might put into the table. In a real worksheet you would locate the table so as to leave many lines free for adding new ranges. The macro: C D E 3 Range Names Description 4 """""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" 5 1 SUM Sum of monthly sales figures. 6 2 NAMES Names of salesmen. 7 8 9 10 11 \T /xlEnter Range Name: ~RangeName:~ 12 /xlDescription: ~Scratch~ 13 /rncHere~~ 14 {Goto}Range Names~{End}{down}{down}{1+{up}~{edit}{calc}~ 15 {right}/CRangeName:~~{right}{/cScratch~~ 16 {Goto}Here~/rndHere~ 17 /rnc 18 RangeName: 19 ~ 20 21 Scratch Editor's Note: In PC Mag Vol 4 No 16 "Making a Table of 1-2-3 Range Addresses" (above), I included a different macro that builds a table of named ranges. It is based on a routine that prints out the cell addresses of named ranges. If this macro incorporated this routine, the table would include a column giving the cell addresses of each named range as well as a description line. ----------------------------------------------------------------- Macro That Names Ranges of Unknown Lengths (PC Magazine Vol 4 No 23 Nov 12, 1985 Spreadsheet Clinic) You may sometimes find you have to write a 123 macro that assigns a name to a range of cells. For example, you may need to name the results of a data query so that you can sort or copy the output. Most of the time this is easy: Send the cursor to the beginning of the range and use {End}{Down}{End}{Right} to find the limits of the range. You can run into problems, though, if the macro tries to name a range that is only one row deep. In this case, if you go to the first (and only) row in the range and use {End}{Down}, you could send the cursor to the far depts of the worksheet. The macro below solves this problem. You can use it to name a range of only one row. For the trick to work, though, you have to start naming the range one cell above the acual area you are interested in. In this example, start the macro from cell C4, even though the data query output you want to name begins in cell C5. What's in row 4 doesn't matter; it just provides space above the cells you want to name. The macro starts off normally. It sends the cursor to C4 and uses END and the arrow keys to define the range. If the macro stopped there, though, the range would include the unnecessary row above the range. Cell D14 in the macro gets rid of this by using a technique to move the "free cell." While you are "pointing" to define a range, each time you hit the period key, the free cell moves clockwise to another corner of the range. The two periods in cell D14 move the free cell from the lower right-hand corner to the top of the range. The {Down} command then takes row 4 out of the range, and the final carriage return defines the range. Although this technique works with one-row ranges, you can still run into problems if there are no records that match your data query and the output area is blank. Your macro may need to test the output area to make sure it contains data before it calls this routine. Editor's Note: As long as you are working with data query output that is at least several columns wide, you won't have to worry about {End}{Right} taking the cursor for a joy ride. But what happens if your target range is just one cell? The macro can be modified to deal with that problem, too. Just make the starting cell one column to the left as well as one row above the raget range and change cell D14 to read: ..{Down}{Right}~ C D E F 4 Name Address City State 5 Smith, Francis J. 127 Canton Avenue Providence RI 6 7 8 9 \N {goto}C4~ Goto row above target area 10 /rndOUTPUT~ Clear previous name use 11 /rncOUTPUT~ Create range name OUTPUT 12 {end}{down} Find lower limit 13 {end}{right} Find rightmost limit 14 ..{down}~ Move free cell clockwise to 15 top of range and move it 16 down one row to finish 17 defining the area to be 18 named ----------------------------------------------------------------- Hiding Notes Within a Worksheet (PC Magazine Vol 4 No 16 Aug 6, 1985 Spreadsheet Clinic) When building 123 or Symphony models to be used by other people, make a point of annotating them. Describe all assumptions and, when appropriate, the rationale behind an assumption. However, if the annotations are in the worksheet itself, the result can be visually confusing, and if they are on paper, it may be awkward for the user to refer to them while he is running the model. To "hide" annotations within a worksheet, create a new column, only one character wide, to the left of the values you want to annotate. If, when you type notes into that column, you begin with a space or two, the column will normally appear to be empty. When the user needs to read your notes, however, he can move the cursor to that column, and a line of text will appear in the control panel. If he wants to read all the text at once, he can temporarily widen the notes column. Editor's Note: This is the best way to annotate a worksheet, though you may need to watch for a few things. For example, when you're typing in a notation on the second line of the control panel, you can use up to 80 characters. But once that note is entered, and a user wants to read the contents by putting the cursor on that cell, the note will appear in the top line of the control panel, which displays only 67 characters. Hitting the F2 key will bring back all 80 characters, but not all spreadsheet users will think of that. Another point to remember is that a long annotation will show up in any blank cells to the right even if the adjacent cell to the right has something in it. Let's say column A contains your notes, columns B and C contain values, and column D is blank for aesthetic reasons. Your note text won't appear in B or C but, if it's long enough, it will show through in D, which is blank. ----------------------------------------------------------------- Hiding Spreadsheet Notes (PC Magazine Vol 4 No 23 Nov 12, 1985 Spreadsheet Clinic) In your response to "Hiding Notes Within a Spreadsheet" (Vol 4 No 16) (above), you pointed out that if the columns to the right of the note are blank, the note text will spill into them. You can avoid this by starting notes with \ and as many spaces as there are characters in the notes column. The entire spreadsheet may be blank, but you still won't see the notes until you move the cursor to them. ----------------------------------------------------------------- Cell Mating (PC Magazine Vol 4 No 18 Sept 3, 1985 Spreadsheet Clinic) This macro combines the contents of two different 123 worksheet cells and writes them to a third cell. In the example below, the macro (lines 3 to 33) combines the book titles in cells C37..C39 with the authors in cells D37..D39, puts the word "by" between the two, and writes the results to cells E37..E39. The macro works on the principle of filling in "holes" in the macro (cells D11 and D13) with variables, and operating on those variables when the macro processor gets to those cells. The macro uses several predefined range names, but in every case the range is the cell to the right of the range name in cells C3..C33. The macro can also be used to combine strings of any kind. Editor's Note: There is a way both to simplify the macro and to eliminate the awkward series of {del}s (lines 14 and 15) in the process. All you have to do to accomplish this is to remove lines 9, 14, 15, 32, and 33, and to replace line 20 with /reEDIT~. Then give a blank cell the range name EDIT, so as to put that part of the "work area" outside the macro. The routine will then run a little more quickly and will take up fewer lines. C D E F 3 \C {goto}c37~ Cursor to first title field 4 CONTINUE /rncTITLE~~ Name the field TITLE 5 /c~TITLE_X~ Copy title to work area 6 {right} Cursor to author field 7 /c~AUTHOR_X~ Copy author to work area 8 {goto}EDIT~ Cursor to work area 9 EDIT {edit}{home} Edit work area 10 ' Label prefix 11 TITLE_X Read title 12 by Insert " by " 13 AUTHOR_X Read author 14 {del}{del}{del}{del}{del}{del} Delete "'{edit}{home} from 15 {del}{del}{del}{del}{del}{del}{del} the work area 16 ~ End edit 17 {goto}TITLE~ Cursor to TITLE 18 {right}{right} Cursor to Title/Author field 19 /cEDIT~~ Copy work area to Title/Author 20 /cEDIT_COM~EDIT~ Replace edit command 21 {goto}TITLE~ Cursor to title field 22 /rncEND_CHECK~{down}~ Create range to check for end 23 /xi(@count(END_CHECK)=1)~/xgEND~ If next title field empty, END 24 /rndEND_CHECK~ Delete range for end check 25 /rndTITLE~ Delete old TITLE 26 {down} Cursor to next title field 27 /xgCONTINUE~ Loop back to beginning 28 29 END /rndEND_CHECK~ Delete range for end check 30 /rndTITLE~ Delete old TITLE 31 /xq Quit 32 33 EDIT_COM {edit}{home} Edit command replacement 34 35 36 TITLE AUTHOR TITLE/AUTHOR 37 Rob Roy Walter Scott Rob Roy by Walter Scott 38 Burmese Days George Orwell Burmese Days by George Orwell 39 Main Street Sinclair Lewis Main Streen by Sinclair Lewis 40 Here at PC Ruth Saperstein Here at PC by Ruth Saperstein 41 Digging Eqypt Rosetta Stone Digging Egypt by Rosetta Stone 42 Moby Dick H. Melville Moby Dick by H. Melville ----------------------------------------------------------------- 1-2-3 as a Diskette Librarian (PC Magazine Vol 4 No 19 Oct 1, 1985 Spreadsheet Clinic) With very little work, you can turn 123 into a diskette librarian that's better than several special-purpose programs, including IBM's diskette librarian. All you need is to create ASCII files of your disk directories and import them into a 123 spreadsheet. To create an ASCII directory file, just redirect the output of the DOS DIR command to a file on disk with the following command: DIR d:>d:filename.PRN. Don't forget to give the file a PRN extension, because 123 won't import files with any other extension. Import the disk directory file with the /File Import Text command. You will get an error message saying "Part of file is missing" when you hit Enter. Ignore that message. Just hit Enter again and the disk directory file will appear in your worksheet just below the cursor. (You get the error message because the DOS pipe used to create the directory file doesn't put a ^Z at the end.) Use the same import command to read as many diskette directories as you like into the same worksheet. Identify the contents of each diskette with a DOS volume label, or give each directory in the worksheet a name, and write that name on the diskette's paper label. You will have plenty of room to identify each disk file within the 123 worksheet because you can put up to 240 characters in a single cell. (If you plan to print out your disk library, you had better limit your comments to your printer's maximum line length.) If you keep your library up to date, you'll never have to worry again about where you put a file. Editor's Note: An added bonus of this technique is that you can use 123's data commands to sort and search your disk directories. Also, if you don't need all information in a standard directory entry (such as the date and time when the file was last written), you can start your remarks in a column that overlaps that information and thus covers it up. If you decide you do need the date and time after all, just move the cursor back to the cell containing the directory entry, and all the original information will then appear in the first line of the control panel. ----------------------------------------------------------------- Word Processing with 1-2-3 (PC Magazine Vol 4 No 21 Oct 15, 1985 Spreadsheet Clinic) With one small macro that uses the /Range Format command, you can turn 123 into a fairly workable word processor. First, set column width of A to 6 (or whatever you want your left margin to be). Set the column width of B to 60 (or however many characters you want in a line of text). Then, off out of the way, in column D or #, type the macro: '{?}~/rj~{end}{down}/xg\W~ Name this macro \W, put the cursor in column B, hit Alt-W and go. Type text until you have finished a paragraph and hit Enter. Your paragraph will format itself nicely inside column B. If you type more than 240 characters, 123 will beep, and you will have to hit Enter even if you're not at the end of a paragraph. Just hit the edit key (F2) and keep on going until you finish the paragraph. Hit Enter again. Your paragraph will look fine. Hit the down-arrow key once or twice to begin a new paragraph. Warning: if you type only one line of text and hit Enter, you will suddenly find yourself at the bottom of the worksheet. End-up-arrow will get you back. While you are typing, you can use all of 123's Edit Mode commands. You can also back up to a paragraph you wrote earlier, hit the edit key, make changes, and hit Enter when you're finished. Your text will even automatically realign itself to reflect the changes. For typing address blocks, dates, titles, and so on, use the " or ^ label prefixes to right-justify or center text. When you begin a paragraph with a number, be sure to start with ' so that 123 will know it's a label. Also, all the normal 123 commands are still available to you. You can Move, Copy, Erase, alphabetize, and include spreadsheet date in your text. Just be sure to hit Ctrl-Break to get out of the macro before you use any / commands. When you are finished with your text, print columns A and B or write a macro that prints as much of your worksheet as necessary. Editor's Note: This technique can do something a lot of high- priced word processors can't: multiple columns. If you reset column B's width to 25 or 30, you'll get a nice, skinny column of text. Use the /Move command to rearrange it on the page to your liking. To not suddenly find yourself at row 2048 because of a one-line paragraph, put an IF statement (/xi) in the macro: \W {?}~/rj~/rncTEMP~{bs}.{down}~/xi@count(TEMP)>=2~{end}{down}/xg\w~ /xg\w~ With this modification it does an {end}{down} routine only when there are two or more lines in a paragraph. You get some unwanted flash and boom each time the macro executes, but his way you'll never end up staring at a blank screen. ----------------------------------------------------------------- Many Happy Returns (PC Magazine Vol 4 No 21 Oct 15, 1985 Spreadsheet Clinic) When you use 123 or Symphony, there is an easy way to look at any part of a large spreadsheet and then return to exactly where you were before. The trick is to hit the + key in Ready mode to make the program think you are going to enter a formula. You can then page around and look at whatever you like. When you're done, just hit Escape and you'll be right back where you started. Hit Escape again and the + sign will disappear from the command line, and leave the original cell entry unchanged. This technique also works when you're entering a real formula; it's designed to let you look around for any cell addresses you might need to put into a formula. To get back to the original cell, hit Escape only once. If you hit it again, you'll wipe out your formula. ----------------------------------------------------------------- Getting Rid of Unwanted Zeros (PC Magazine Vol 4 No 21 Oct 15, 1985 Spreadsheet Clinic) Many people use 123 to print invoices and other tabular data. The example below is a typical sample, in which the only calculations are #Units x Cost = Total, and an @sum of the Total column to give a grand total. The usual way to set up such an invoice would be to leave out the numbers in the #Units and Cost columns, and to fill the Total column with #Units x Cost formulas, such as +B5*D5. Until the numbers are filled in for #Units and Cost, the Total column contains a string of zeros. Even after you have completed the invoice, you will still have zeros left in those rows for which there are no #Units or Cost figures (rows 9 through 15 below). This makes for unprofessional invoices. If you use the two-line macro below, you'll never have those unwanted zeros again. Also, you need only one #Units x Cost formula (+B5*D5), in cell F5. Just fill the #Units and Cost numbers in the invoice and run the macro with Alt-C. The first line copies the formula in F5 to the entire Total column. The second does a /Data Query according to the ranges described in column H and eliminates all figures in the Total column that are equal to $0.00. A B C D E F G H -------------------------------------------------------------- 4 #UNITS | COST | TOTAL | CRITERION RANGE 5 10 | $25.00 | $250.00 | --------------- 6 5 | $5.00 | $25.00 | TOTAL 7 6 | $7.00 | $42.00 | $0.00 8 30 | $15.00 | $450.00 | 9 | | $0.00 | 10 | | $0.00 | 11 | | $0.00 | RANGES 12 | | $0.00 | --------------- 13 | | $0.00 | TOTAL = F5..F15 14 | | $0.00 | INPUT = F4..F15 15 | | $0.00 | CRIT = H6..H7 16 ---------- 17 GRAND TOTAL $767.00 \C /Cf5~TOTAL~ 'Copy +B5*D5 to TOTAL column /DQIinput~Ccrit~DDQ 'Delete data records that match $0.00 ----------------------------------------------------------------- Rows to Columns and Vice Versa (PC Magazine Vol 4 No 21 Oct 15, 1985 Spreadsheet Clinic) Occasionally you may have to modify a worksheet by changing the rows into columns or the columns into rows. There are two obvious ways to do this: 1) move or copy each cell to a new location one cell at a time, or 2) with cells containing values, write formulas to reproduce the values in different cells. Neither way is efficient. Make a copy of your worksheet and put the original away. Start 123 by typing LOTUS and choose Translate from the Access System menu. Translate your worksheet from WKS to DIF and then translate it back from DIF to WKS. When you make this second translation, you will get a prompt asking whether you want the translation by columns or by rows. Choose row-wise translation, and your original columns will become rows and your rows will become columns. If you extract a section of the worksheet to translate and the program hangs up, it probably means you have partial range name definitions within the extract. Strip all range names from the extract with the /Range Name Reset command, save the file, and try the translation again. Translation removes range names and cell formats anyway, so you might as well do it yourself to avoid problems. Editor's Note: The procedure does more than remove range names and cell formats; it also turns all formulas into values. Further, it produces a weird sort of mirror image of the original rows and columns. For example, a column that started in cell B4 (2 across and 4 down) will turn into a row that begins at D2 (4 across and 2 down). Still, if all you need is to move values and labels around, it beats any other method. ----------------------------------------------------------------- Two More on 1-2-3 (PC World October 1985 Star-Dot-Star) When writing macros, users are accustomed to pressing the ESCape key to back out of a menu. However, when ESCape is pressed within a macro, 123 goes to whatever follows the /XM statement, causing the macro to overwrite a cell with the macro menu labels. This can be a serious problem if the cursor was over an important cell. To avoid this situation, leave a blank line between the cell containing the /XM statement and the first menu item. Using the @SUM function is much more efficient for the use of long addition formulas such as +A1+B5+C8+F9+H17+I15+P46+Q55+AA15+AB15. For example, the formula @SUM(A1,B5,C8,F9,H17,I15,P46,Q55,AA15,AB15) uses less memory as well as less disk space than it does when entered as a string of addition. ----------------------------------------------------------------- Entering Zip Codes (PC Magazine Vol 4 No 22 October 29, 1985 Spreadsheet Clinic) Entering zip codes can be a problem in 123, especially when they begin with 0. 123 doesn't allow a numeric entry to being with a 0 and strips it from the front of the number. This macro makes things easy: \Z '{?}~{down}/xg\Z~ The macro automatically assigns a label prefix to your cell entry, waits for the entry, moves to the next cell and then repeats itself. This way zip coes can begin with 0. You get out of the endless loop with Ctrl-Break. Editor's Note: Zip codes become labels instead of numbers in this case, but that should make no difference. You can still use the /Data Sort command to sort them, and no one does airthmetic on zip codes. This macro is handy for more than zip codes. You can use it any time you want to enter numbers or math symbols as labels. It would be useful for writing macros, for example, where it's easy to forget that you have to precede every / at the beginning of a line with a label prefix. You can modify this macro for row work by replacing {down} with {right}. Finally, you might find it useful to replace the initial ' with ^ or " if you want to center or right-justify your labels. ----------------------------------------------------------------- Inserting Blank Lines in a Spreadsheet (PC Magazine Vol 4 No 23 Nov 12, 1985 Spreadsheet Clinic) Sometimes you have to insert blank lines between the existing lines of a spreadsheet. It's easy enough to write a macro to insert them, but if there are many blank lines to insert, the macro will take a long time to execute. Also, when you use the Insert command, it will put new rows into the entire spreadsheet, whereas you may want more space in just one (vertical) part of the worksheet and may prefer to leave the rest of it unchanged. The figure below illustrates this technique. Assume that you want to insert blank spaces between the spreadsheet data in column D of the "Before" illustration; when you're finished, the data should look like the "After" illustration. The first step is to move any data in rows 10 through 16 of column D out of the way to make as many blank rows as you have rows of spreadsheet data. Next, use the /Data Fill command to enter a number series in cells C2 to C9. Start with 1 but choose an increment of 2, so that you get the odd number series 1 to 13. Then go End-Down to get to the bottom of the sequence, and use /Copy. End-Up Down Return to make an exact copy of the odd number series in cells C10 to C16. This is an intermediate step. Now move to C10, the first cell of the sequence just copied, and do another /Data Fill. Hit the backspace when 123 offers the previous data fill range and then go End-Down Return. It was the previous copy step that let's you specify the fill range with End-Down. It's important that the two number sequences be the same length, and this is the easiest way to be sure that they are. 123 now proposes a data fill starting value of 1, but enter 2 and hit Return twice. The new sequence in cells C10 through C16 is all even numbers, and the screen looks exactly like the "Before" illustration. Now all you need to do is sort columns C and D with column C as the primary sort key. The odd and even numbers in column C fall into sequence and, in addition, the data is double-spaced, as shown in the "After" illustration. And you haven't inserted a single new row into the spreadsheet; only the part you sorted has changed. Thus, if you had data in columns E and beyond, it would remain untouched. If you need to insert two blank lines between each row of data, use the same technique but make three data fill sequences, using step 3 instead of step 2. Start the first sequence with 1, the second with 2, and the last sequence with 3. === BEFORE === === AFTER === C D C D 3 1 Sample data 3 1 Sample data 4 3 Sample data 4 2 5 5 Sample data 5 3 Sample data 6 7 Sample data 6 4 7 9 Sample data 7 5 Sample data 8 11 Sample data 8 6 9 13 Sample data 9 7 Sample data 10 2 10 8 11 4 11 9 Sample data 12 6 12 10 13 8 13 11 Sample data 14 10 14 12 15 12 15 13 Sample data 16 14 16 14 ----------------------------------------------------------------- Preventing Escape from a Menu (PC Magazine Vol 4 No 24 Nov 26, 1985 Spreadsheet Clinic) With normal 123 menus it's possible to hit the Escape key to back up through menu choices and eventually to return to the ready mode. When you write your own menu macros, however, the Escape key will not necessarily get you back to ready mode. Instead, it causes the macro to skip the menu and continue on with the commands in the cell below the /XM command. You may not, however, want users to be able to avoid a menu choice by hitting the Escape key, as illustrated below. The names in column E are range names for the cells to their right. (Cell F14 has both a macro name -- \A to start the menus -- and a range name.) The trick to keeping the user within the menus is to put a /XG (goto) command just below the cells that invoke menus. In this case, cells F14 and F17 contain the /XM commands that invoke the main menu and the print menu (F4..H7 and F9..H12). If the user hits Escape after either menu appears, the gotos in F15 and F18 will send him right back to F14, which invokes the main menu. The /XG commands at the ends of the menu options (cells F7, G7, F12, G12, H11) send the user back to the main menu after the macro has saved the file or printed the report. Thus, the only way out of the menus -- aside from hitting Ctrl-Break -- is to exit via the last choice in the main menu. E F G H 4 MAIN Print Save End 5 Print the report Save the file End program 6 /xgToPrint~ /fsFILENAME~ /wey 7 /xgToMAIN~ /xgToMAIN~ 8 9 Print Align Page Quit 10 Align page Page up Quit Print 11 /ppagq /pppgq /xgToMAIN~ 12 /xgToMAIN~ /xgToMAIN~ 13 14 ToMAIN /xmMAIN~ 15 & \A /xgToMAIN~ 16 17 ToPrint /xmPrint~ 18 /xgToMAIN~ ----------------------------------------------------------------- Adding Sound Effects to 1-2-3 Messages (PC Magazine Vol 4 No 24 Nov 26, 1985 Spreadsheet Clinic) The /XL and /XN commands in the 123 macro language display a message and wait for keyboard input. They are normally used in interactive 123 models to request information from the user. But you can also use them for error messages. You can even make the computer beep in conjunction with an /XL or /XN message simply by writing a message that is longer thatn 123's 39-character limit. The macro below shows two uses of the /XN command, the first as a prompt for input, the second as both a prompt for input and an error message. In the first /XN command, the prompt message is less than 39 characters long, so the computer will not beep. In the second /XN command, which operates only if the user makes an error, the spaces between "Try Again" and the tilde that follows stretch the message past the 39-character limit. The computer will beep once for each space past 39 characters, but since the spaces follow each other immediately, they have the effect of producing one long beep. This trick signals errors and will not interfere with the operation of the macro in any way. Editor's Note: When you use /XN or /XL strictly as an error message, 123 still waits for user input. Since /XL will accept any kind of input, but /XN requires numbers, it's best to use /XL. In either case, you still have to include a cell address where the user input will be stored. The input usually doesn't matter, so you can tuck it out of the way in a corner of the worksheet and ignore it. ----------------------------------------------------------------- Total Solution (PC World November 1985 Star-Dot-Star) Often a row and a column of a worksheet should yield equal totals. 123 offers no easy way to check this, since the spreadsheet can place only one value in a cell, either the sum of the row or the sum of the column. The formula below compares two totals from a hypothetical worksheet. If they match, the program displays the total; if the values differ, you'll see "ERR". @if(@sum(g5..g16)=@sum(B17..F17),@SUM(G5..G16),@ERR) Change the cell references to match those of the row and column you wish to compare. ----------------------------------------------------------------- On Menus and Memory (PC Magazine Vol 4 No 25 Dec 10, 1985 Spreadsheet Clinic) If you write 123 menus with the /XM command, you will find that many people back out of them by hitting Escape. That works fine with 123's own menus, but not always with the ones you build yourself. When 123 is processing a macro-written menu, hitting Escape just makes the macro processor move on to the next cell in the macro. The sample menu macros below will run differently even though the blank space in cell D14 is the only difference between them. If you run the first macro and then hit Escape to get out of the menu, the macro processor will go on and read cells Dy, D7 and D8, even though they are text for the menu rathen than macro commands. Because of the carriage return in cell D8, the macro will write that text into whatever cell the cursor happens to be on. If that cell had something important it it, it would be gone. If you run the second macro and hit Escape to get out, you'll return smoothly to the ready state. This is because the macro processor finds a blank cell at D14 and terminates the macro. D E F 3 4 ** First Menu ** 5 /xmD6~ 6 Income Balance Quit 7 Print income statement Print balance sheet Return to ready mode 8 /ppcrrINCOME~q /ppcrrBALANCE~q 9 10 11 12 ** Second Menu ** 13 /xmD15~ 14 15 Income Balance Quit 16 Print income statement Print balance sheet Return to ready mode 17 /ppcrrINCOME~q /ppcrrBALANCE~q The two different formulas below do the same thing; however, you will use less memory and disk space if you use the formula with the @sum function. This is because 123 knows that the operator is + for every cell in the second formula, whereas it must ust memory and disk space to record each separate operator in the first formula. +A1+B5+C8+F9+H17+I15+P46+Q55+AA15+AB28+BG67+BA15+CB45+N47+I16 @sum(A1,B5,C8,F9,H17,I15,P46,Q55,AA15,AB28,BG67,BA15,CB45,N47,I16) Editor's Note: The 123 manual clearly explains that hitting Escape when in a macro-built menu will make the macro keep executing from the cell below the /XM command. Since the /XM command includes the address of the cell where the menu text begins, the text doesn't have to be right under the /XM command and thus directly in the path of the macro. A common solution is to keep menu texts in a column away from the macro. This allows you to include macro commands below the /XM command that will continue to execute if the user refuses to make a menu choice and hits Escape instead. The above solution works well, however, and it has the advantage of keeping the menu text close to the /XM command. This is handy when you're debugging or modifying a macro. A large spreadsheet which included the two different formulas was used to test the second tip. The @sum formula took up 98 bytes of memory, whereas the + formula took up 110 bytes, which constituted a savings of 12 bytes. Both formulas, however, increased the spreadsheet's disk space by exactly the same amount -- 128 bytes. ----------------------------------------------------------------- Utility Macros (PC Magazine Vol 4 No 26 Dec 10, 1985 Spreadsheet Clinic) It's very common to write a formula, such as A12/B16, in which the values in the formula depend on other cells. Occasionally one of those cells may have the value zero. If the formula calls for multiplication by zero, there's no problem, but if B16 turned out to be zero, the formula would now call for division by zero and you'd get an ugly "ERR" message. The macro: \E {edit}{home}@if(CELLX=0,0,{end})~ is a cosmetic device to get rid of that message and display a zero instead. Just replace CELLX with the range name or address of the value that might be zero. The macro tests CALLX to see if it's zero and if it's not, it leaves your formula alone. To use the macro, move the cursor to the cell with the formula in it and hit Alt-E. This macro speeds up the @SUM function when you want to total a column of numbers: \S @sum({end}{up}.{end}{up})~ Just move the cursor to a cell two rows below the column to be totaled and hit Alt-S. If you want the total put immediately below your column of numbers, remove the first {end} from the macro and run it with the cursor in the cell just below the column. Note that other @functions, like @MIN, @STD, @VAR, @AVG, and so on will work in the same macro. This macro converts a decimal number into a percent: \P {edit}{home}({end})*100~/rff2~~ While you can do the same thing with 123's own percent format, you always get the % sign shown along with your number. The macro is handy if you have a column in a spreadsheet that is title "Percentage," and you don't want a % sign in every cell. Using the macro, all you have to do is move to the cell you want to edit and hit Alt-P. ----------------------------------------------------------------- Naming and Storing Macro Routines (PC Magazine Vol 4 No 26 Dec 10, 1985 Spreadsheet Clinic) Whenever you write a complex 123 macro, split it into separate routines, each with a name that describes its function. You can then call these routines from a single "main-line" macro, similar to the subroutine calls of structured programs. After the subroutine runs, it hands control back to the main program. It is helpful to give a range name to the entire subroutine macro, not just to the first cell. The operation of the macro is unchanged because a GOTO command always takes you to the first cell anyway. But now you can use /Worksheet Move to move the macro around within the worksheet. Even better, you can make a library of subroutines, saving them together in one spreadsheet under descriptive names. Whenever you need a subroutine in your current spreadsheet, read it in from disk with /File Copy Combine Name. -----------------------------------------------------------------